{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "applied-ferry",
   "metadata": {},
   "source": [
    "高中体侧数据转换"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "thorough-benchmark",
   "metadata": {},
   "source": [
    "#### 数据加载"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "surface-italian",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 477 entries, 0 to 476\n",
      "Data columns (total 11 columns):\n",
      " #   Column   Non-Null Count  Dtype  \n",
      "---  ------   --------------  -----  \n",
      " 0   班级       477 non-null    int64  \n",
      " 1   性别       477 non-null    object \n",
      " 2   男1000米跑  477 non-null    object \n",
      " 3   男50米跑    477 non-null    float64\n",
      " 4   男跳远      477 non-null    float64\n",
      " 5   男体前屈     477 non-null    int64  \n",
      " 6   男引体      477 non-null    int64  \n",
      " 7   男肺活量     477 non-null    int64  \n",
      " 8   身高       477 non-null    float64\n",
      " 9   体重       477 non-null    float64\n",
      " 10  BMI      477 non-null    int64  \n",
      "dtypes: float64(4), int64(5), object(2)\n",
      "memory usage: 41.1+ KB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2785</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>3133</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>3901</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>4946</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13</td>\n",
       "      <td>9</td>\n",
       "      <td>3538</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'23</td>\n",
       "      <td>8.27</td>\n",
       "      <td>208.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>4647</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5'19</td>\n",
       "      <td>9.55</td>\n",
       "      <td>210.0</td>\n",
       "      <td>15</td>\n",
       "      <td>6</td>\n",
       "      <td>7042</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3'25</td>\n",
       "      <td>7.50</td>\n",
       "      <td>252.0</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>5755</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'39</td>\n",
       "      <td>7.81</td>\n",
       "      <td>208.0</td>\n",
       "      <td>14</td>\n",
       "      <td>11</td>\n",
       "      <td>5688</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别 男1000米跑  男50米跑    男跳远  男体前屈  男引体  男肺活量     身高    体重  BMI\n",
       "0     1  男    4'13   8.88  195.0    12    1  2785  170.0  72.6    0\n",
       "1     1  男    4'16   7.70  225.0    11    7  3133  174.0  52.7    0\n",
       "2     1  男    4'09   8.45  218.0    14    1  3901  169.0  46.5    0\n",
       "3     1  男    4'21   8.05  206.0    13    1  4946  183.0  79.7    0\n",
       "4     1  男    3'44   7.52  210.0    13    9  3538  171.0  54.7    0\n",
       "..   .. ..     ...    ...    ...   ...  ...   ...    ...   ...  ...\n",
       "472  17  男    4'23   8.27  208.0    10    0  4647  176.0  69.5    0\n",
       "473  17  男    5'19   9.55  210.0    15    6  7042  177.0  76.0    0\n",
       "474  17  男    3'25   7.50  252.0    13   13  5755  181.0  65.0    0\n",
       "475  17  男    4'39   7.81  208.0    14   11  5688  172.0  51.7    0\n",
       "476  17  男       0   0.00    0.0     0    0     0    0.0   0.0    0\n",
       "\n",
       "[477 rows x 11 columns]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "data_man = pd.read_excel('./18级高一体测成绩汇总.xls') # 默认加载第一个工作表\n",
    "data_woman = pd.read_excel('./18级高一体测成绩汇总.xls',sheet_name = 1) # 加载第二个工作表\n",
    "data_man.shape #(477, 11)\n",
    "data_woman.shape #(593, 11)\n",
    "print(data_man.info())\n",
    "data_man\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dress-danger",
   "metadata": {},
   "source": [
    "#### 评分标准加"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "israeli-incentive",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 20 entries, 0 to 19\n",
      "Data columns (total 24 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   (男肺活量, 成绩)     20 non-null     int64  \n",
      " 1   (男肺活量, 分数)     20 non-null     int64  \n",
      " 2   (女肺活量, 成绩)     20 non-null     int64  \n",
      " 3   (女肺活量, 分数)     20 non-null     int64  \n",
      " 4   (男50米跑, 成绩)    20 non-null     float64\n",
      " 5   (男50米跑, 分数)    20 non-null     int64  \n",
      " 6   (女50米跑, 成绩)    20 non-null     float64\n",
      " 7   (女50米跑, 分数)    20 non-null     int64  \n",
      " 8   (男体前屈, 成绩)     20 non-null     float64\n",
      " 9   (男体前屈, 分数)     20 non-null     int64  \n",
      " 10  (女体前屈, 成绩)     20 non-null     float64\n",
      " 11  (女体前屈, 分数)     20 non-null     int64  \n",
      " 12  (男跳远, 成绩)      20 non-null     int64  \n",
      " 13  (男跳远, 分数)      20 non-null     int64  \n",
      " 14  (女跳远, 成绩)      20 non-null     int64  \n",
      " 15  (女跳远, 分数)      20 non-null     int64  \n",
      " 16  (男引体, 成绩)      15 non-null     float64\n",
      " 17  (男引体, 分数)      20 non-null     int64  \n",
      " 18  (女仰卧, 成绩)      20 non-null     int64  \n",
      " 19  (女仰卧, 分数)      20 non-null     int64  \n",
      " 20  (男1000米跑, 成绩)  20 non-null     object \n",
      " 21  (男1000米跑, 分数)  20 non-null     int64  \n",
      " 22  (女800米跑, 成绩)   20 non-null     object \n",
      " 23  (女800米跑, 分数)   20 non-null     int64  \n",
      "dtypes: float64(5), int64(17), object(2)\n",
      "memory usage: 3.9+ KB\n"
     ]
    }
   ],
   "source": [
    "rule = pd.read_excel('./体侧成绩评分表.xls',header = [0,1]) #header=[0,1]表示多层列索引\n",
    "rule.describe()\n",
    "rule.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "id": "micro-theorem",
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'list' object has no attribute 'strip'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-186-853a71c6cf44>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[1;31m# print(m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;31m# a=[]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 4\u001b[1;33m \u001b[1;33m[\u001b[0m\u001b[0mm\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstrip\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m''\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      5\u001b[0m \u001b[1;31m# for i in m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      6\u001b[0m \u001b[1;31m#     i = i.replace(\"'\",'.').replace('\"','')\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mAttributeError\u001b[0m: 'list' object has no attribute 'strip'"
     ]
    }
   ],
   "source": [
    "# m = np.array(rule['男1000米跑']['成绩'])\n",
    "# print(m)\n",
    "# a=[]\n",
    "# [m].strip('')\n",
    "# for i in m:\n",
    "#     i = i.replace(\"'\",'.').replace('\"','')\n",
    "#     rule.replace(['男1000米跑']['成绩'],i)\n",
    "# a = np.array(a)\n",
    "# a = a.astype(np.float)\n",
    "# print(a)\n",
    "# rule.replace(['男1000米跑']['成绩'],a)\n",
    "# rule.replace(['男1000米跑']['成绩'],a)\n",
    "# rule['男1000米跑']['成绩'] = rule['男1000米跑']['成绩'].astype('float')\n",
    "# rule['男1000米跑']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "id": "french-custom",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4.21\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.30</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.35</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.40</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.47</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.55</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4.00</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4.05</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4.10</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4.15</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4.20</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>4.25</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>4.30</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>4.35</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>4.40</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>4.45</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>5.05</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>5.25</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>5.45</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>6.05</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>6.25</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      成绩   分数\n",
       "0   3.30  100\n",
       "1   3.35   95\n",
       "2   3.40   90\n",
       "3   3.47   85\n",
       "4   3.55   80\n",
       "5   4.00   78\n",
       "6   4.05   76\n",
       "7   4.10   74\n",
       "8   4.15   72\n",
       "9   4.20   70\n",
       "10  4.25   68\n",
       "11  4.30   66\n",
       "12  4.35   64\n",
       "13  4.40   62\n",
       "14  4.45   60\n",
       "15  5.05   50\n",
       "16  5.25   40\n",
       "17  5.45   30\n",
       "18  6.05   20\n",
       "19  6.25   10"
      ]
     },
     "execution_count": 275,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rule['男1000米跑']['成绩'].map(process_data)\n",
    "\n",
    "def process_data(x):\n",
    "    #如果x不是字符串\n",
    "    #如果时间是整数直接返回\n",
    "    if not isinstance(x,str):\n",
    "        return x\n",
    "    x = x.strip('\"') # 删除开头或是结尾的字符\n",
    "    # 得到分钟ms秒\n",
    "    m,s = x.split(\"\\'\") # 反斜杠 加分号去除换行\n",
    "#     second = float(s)/(60)\n",
    "    # 如果不是整数时间，返回带小数的分数\n",
    "    a = (m + '.' +s)\n",
    "    return float(a)\n",
    "print(process_data(\"4'21\"))\n",
    "rule_1000 = rule['男1000米跑']['成绩'].map(process_data).astype(float)\n",
    "rule_1000 = pd.DataFrame(rule_100)\n",
    "rule_1000.insert(loc = 1,column='分数',value=rule['男1000米跑']['分数'])\n",
    "# rule_10indexin rule['男1000米跑']['分数']\n",
    "rule_1000\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "id": "minute-twelve",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.24</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.30</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.36</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.43</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.50</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3.55</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4.00</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4.05</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4.10</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4.15</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>4.20</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>4.25</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>4.30</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>4.35</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>4.40</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>4.50</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>5.00</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>5.10</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>5.20</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>5.30</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      成绩   分数\n",
       "0   3.24  100\n",
       "1   3.30   95\n",
       "2   3.36   90\n",
       "3   3.43   85\n",
       "4   3.50   80\n",
       "5   3.55   78\n",
       "6   4.00   76\n",
       "7   4.05   74\n",
       "8   4.10   72\n",
       "9   4.15   70\n",
       "10  4.20   68\n",
       "11  4.25   66\n",
       "12  4.30   64\n",
       "13  4.35   62\n",
       "14  4.40   60\n",
       "15  4.50   50\n",
       "16  5.00   40\n",
       "17  5.10   30\n",
       "18  5.20   20\n",
       "19  5.30   10"
      ]
     },
     "execution_count": 285,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 女生800米改\n",
    "rule_800 = rule['女800米跑']['成绩'].map(process_data)\n",
    "rule_800 = pd.DataFrame(rule_800)\n",
    "rule_800.insert(loc = 1,column='分数',value=rule['女800米跑']['分数'])\n",
    "rule_800"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "signed-gospel",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 477 entries, 0 to 476\n",
      "Data columns (total 11 columns):\n",
      " #   Column   Non-Null Count  Dtype  \n",
      "---  ------   --------------  -----  \n",
      " 0   班级       477 non-null    int64  \n",
      " 1   性别       477 non-null    object \n",
      " 2   男1000米跑  477 non-null    float64\n",
      " 3   男50米跑    477 non-null    float64\n",
      " 4   男跳远      477 non-null    float64\n",
      " 5   男体前屈     477 non-null    int64  \n",
      " 6   男引体      477 non-null    int64  \n",
      " 7   男肺活量     477 non-null    int64  \n",
      " 8   身高       477 non-null    float64\n",
      " 9   体重       477 non-null    float64\n",
      " 10  BMI      477 non-null    int64  \n",
      "dtypes: float64(5), int64(5), object(1)\n",
      "memory usage: 41.1+ KB\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>477.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>477.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>10.4</td>\n",
       "      <td>3.7</td>\n",
       "      <td>7.8</td>\n",
       "      <td>211.5</td>\n",
       "      <td>12.2</td>\n",
       "      <td>7.9</td>\n",
       "      <td>4311.4</td>\n",
       "      <td>170.0</td>\n",
       "      <td>62.2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>4.9</td>\n",
       "      <td>0.9</td>\n",
       "      <td>1.5</td>\n",
       "      <td>38.9</td>\n",
       "      <td>6.2</td>\n",
       "      <td>4.7</td>\n",
       "      <td>1106.0</td>\n",
       "      <td>26.8</td>\n",
       "      <td>14.6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>7.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>7.7</td>\n",
       "      <td>202.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3765.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>55.4</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>11.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4340.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>61.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>14.0</td>\n",
       "      <td>4.2</td>\n",
       "      <td>8.3</td>\n",
       "      <td>230.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4970.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>17.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>295.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>7852.0</td>\n",
       "      <td>196.0</td>\n",
       "      <td>113.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          班级  男1000米跑  男50米跑    男跳远   男体前屈    男引体    男肺活量     身高     体重    BMI\n",
       "count  477.0    477.0  477.0  477.0  477.0  477.0   477.0  477.0  477.0  477.0\n",
       "mean    10.4      3.7    7.8  211.5   12.2    7.9  4311.4  170.0   62.2    0.0\n",
       "std      4.9      0.9    1.5   38.9    6.2    4.7  1106.0   26.8   14.6    0.0\n",
       "min      1.0      0.0    0.0    0.0   -5.0    0.0     0.0    0.0    0.0    0.0\n",
       "25%      7.0      3.5    7.7  202.0    8.0    4.0  3765.0  170.0   55.4    0.0\n",
       "50%     11.0      4.0    8.0  215.0   12.0    8.0  4340.0  174.0   61.5    0.0\n",
       "75%     14.0      4.2    8.3  230.0   16.0   11.0  4970.0  178.0   69.0    0.0\n",
       "max     17.0      5.5   10.0  295.0   31.0   23.0  7852.0  196.0  113.5    0.0"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 男生1000改时间\n",
    "m = np.array(data_man['男1000米跑']) # series 转化成array\n",
    "m = map(str, m) # map转化为字符串\n",
    "a=[]\n",
    "for i in m:\n",
    "    i = i.replace(\"'\",'.')\n",
    "    a.append(i)\n",
    "#男生1000米成绩\n",
    "data_man['男1000米跑'] = a\n",
    "data_man['男1000米跑'] = data_man['男1000米跑'].astype('float')\n",
    "data_man.info()\n",
    "data_man.describe().round(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "bronze-ordinary",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 593 entries, 0 to 592\n",
      "Data columns (total 11 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   班级      593 non-null    int64  \n",
      " 1   性别      593 non-null    object \n",
      " 2   女800米跑  593 non-null    float64\n",
      " 3   女50米跑   593 non-null    float64\n",
      " 4   女跳远     593 non-null    float64\n",
      " 5   女体前屈    593 non-null    int64  \n",
      " 6   女仰卧     593 non-null    int64  \n",
      " 7   女肺活量    593 non-null    int64  \n",
      " 8   身高      593 non-null    float64\n",
      " 9   体重      593 non-null    float64\n",
      " 10  BMI     593 non-null    int64  \n",
      "dtypes: float64(5), int64(5), object(1)\n",
      "memory usage: 51.1+ KB\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>593.0</td>\n",
       "      <td>593.0</td>\n",
       "      <td>593.0</td>\n",
       "      <td>593.0</td>\n",
       "      <td>593.0</td>\n",
       "      <td>593.0</td>\n",
       "      <td>593.0</td>\n",
       "      <td>593.0</td>\n",
       "      <td>593.0</td>\n",
       "      <td>593.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>8.4</td>\n",
       "      <td>30.8</td>\n",
       "      <td>9.7</td>\n",
       "      <td>159.8</td>\n",
       "      <td>15.9</td>\n",
       "      <td>38.3</td>\n",
       "      <td>3010.3</td>\n",
       "      <td>159.7</td>\n",
       "      <td>54.3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>4.5</td>\n",
       "      <td>298.4</td>\n",
       "      <td>1.9</td>\n",
       "      <td>29.0</td>\n",
       "      <td>5.4</td>\n",
       "      <td>8.1</td>\n",
       "      <td>731.1</td>\n",
       "      <td>18.3</td>\n",
       "      <td>9.9</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.4</td>\n",
       "      <td>9.6</td>\n",
       "      <td>152.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>2577.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>8.0</td>\n",
       "      <td>3.5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>2987.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>53.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>12.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.3</td>\n",
       "      <td>171.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>3463.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>58.6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>17.0</td>\n",
       "      <td>4012.0</td>\n",
       "      <td>13.9</td>\n",
       "      <td>220.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>6435.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>92.8</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          班级  女800米跑  女50米跑    女跳远   女体前屈    女仰卧    女肺活量     身高     体重    BMI\n",
       "count  593.0   593.0  593.0  593.0  593.0  593.0   593.0  593.0  593.0  593.0\n",
       "mean     8.4    30.8    9.7  159.8   15.9   38.3  3010.3  159.7   54.3    0.0\n",
       "std      4.5   298.4    1.9   29.0    5.4    8.1   731.1   18.3    9.9    0.0\n",
       "min      1.0     0.0    0.0    0.0    0.0    0.0     0.0    0.0    0.0    0.0\n",
       "25%      5.0     3.4    9.6  152.0   13.0   35.0  2577.0  158.0   49.7    0.0\n",
       "50%      8.0     3.5   10.0  162.0   16.0   40.0  2987.0  162.0   53.5    0.0\n",
       "75%     12.0     4.0   10.3  171.0   19.0   43.0  3463.0  165.0   58.6    0.0\n",
       "max     17.0  4012.0   13.9  220.0   30.0   58.0  6435.0  178.0   92.8    0.0"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 女生1000改时间\n",
    "m = np.array(data_woman['女800米跑']) # series 转化成array\n",
    "m = map(str, m) # map转化为字符串\n",
    "a=[]\n",
    "for i in m:\n",
    "    i = i.replace(\"'\",'.')\n",
    "    a.append(i)\n",
    "#女生1000米成绩\n",
    "data_woman['女800米跑'] = a\n",
    "data_woman['女800米跑'] = data_woman['女800米跑'].astype('float')\n",
    "data_woman.info()\n",
    "data_woman.describe().round(1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "valued-venice",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4540</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4420</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4300</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4050</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3800</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3680</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3560</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3440</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3320</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3200</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3080</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2960</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2840</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2720</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2600</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2470</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2340</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2210</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2080</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1950</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      成绩   分数\n",
       "0   4540  100\n",
       "1   4420   95\n",
       "2   4300   90\n",
       "3   4050   85\n",
       "4   3800   80\n",
       "5   3680   78\n",
       "6   3560   76\n",
       "7   3440   74\n",
       "8   3320   72\n",
       "9   3200   70\n",
       "10  3080   68\n",
       "11  2960   66\n",
       "12  2840   64\n",
       "13  2720   62\n",
       "14  2600   60\n",
       "15  2470   50\n",
       "16  2340   40\n",
       "17  2210   30\n",
       "18  2080   20\n",
       "19  1950   10"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "id": "facial-network",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fhl = rule['男肺活量']\n",
    "def boy_fhl(x):\n",
    "    for data in df_fhl['成绩']:\n",
    "        if x>=data:\n",
    "            return df_fhl['分数'].loc[df_fhl['成绩']==data].values[0]\n",
    "    return 0 \n",
    "boy_fhl(1)\n",
    "ff = data_man['男肺活量'].map(boy_fhl).values\n",
    "data_man.insert(loc = 8,column='男生肺活量成绩',value=ff)\n",
    "# [3,5,7,9,11,13] 男100米跑，男50米跑，男跳远，男体前屈，男引体，男肺活量 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "id": "generic-choice",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_man = data_man.drop(labels='男50米跑成绩',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "id": "fleet-glossary",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男1000米跑成绩</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男50米跑成绩</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男跳远成绩</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男体前屈成绩</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男引体成绩</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>男生肺活量成绩</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>72</td>\n",
       "      <td>8.88</td>\n",
       "      <td>66</td>\n",
       "      <td>195.0</td>\n",
       "      <td>60</td>\n",
       "      <td>12</td>\n",
       "      <td>74</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2785</td>\n",
       "      <td>62</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>70</td>\n",
       "      <td>7.70</td>\n",
       "      <td>78</td>\n",
       "      <td>225.0</td>\n",
       "      <td>74</td>\n",
       "      <td>11</td>\n",
       "      <td>74</td>\n",
       "      <td>7</td>\n",
       "      <td>60</td>\n",
       "      <td>3133</td>\n",
       "      <td>68</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>74</td>\n",
       "      <td>8.45</td>\n",
       "      <td>70</td>\n",
       "      <td>218.0</td>\n",
       "      <td>70</td>\n",
       "      <td>14</td>\n",
       "      <td>78</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3901</td>\n",
       "      <td>80</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>68</td>\n",
       "      <td>8.05</td>\n",
       "      <td>74</td>\n",
       "      <td>206.0</td>\n",
       "      <td>64</td>\n",
       "      <td>13</td>\n",
       "      <td>76</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4946</td>\n",
       "      <td>100</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>85</td>\n",
       "      <td>7.52</td>\n",
       "      <td>78</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66</td>\n",
       "      <td>13</td>\n",
       "      <td>76</td>\n",
       "      <td>9</td>\n",
       "      <td>68</td>\n",
       "      <td>3538</td>\n",
       "      <td>74</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.23</td>\n",
       "      <td>68</td>\n",
       "      <td>8.27</td>\n",
       "      <td>72</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66</td>\n",
       "      <td>10</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4647</td>\n",
       "      <td>100</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.19</td>\n",
       "      <td>40</td>\n",
       "      <td>9.55</td>\n",
       "      <td>50</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66</td>\n",
       "      <td>15</td>\n",
       "      <td>80</td>\n",
       "      <td>6</td>\n",
       "      <td>50</td>\n",
       "      <td>7042</td>\n",
       "      <td>100</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.25</td>\n",
       "      <td>100</td>\n",
       "      <td>7.50</td>\n",
       "      <td>80</td>\n",
       "      <td>252.0</td>\n",
       "      <td>90</td>\n",
       "      <td>13</td>\n",
       "      <td>76</td>\n",
       "      <td>13</td>\n",
       "      <td>85</td>\n",
       "      <td>5755</td>\n",
       "      <td>100</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.39</td>\n",
       "      <td>62</td>\n",
       "      <td>7.81</td>\n",
       "      <td>76</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66</td>\n",
       "      <td>14</td>\n",
       "      <td>78</td>\n",
       "      <td>11</td>\n",
       "      <td>76</td>\n",
       "      <td>5688</td>\n",
       "      <td>100</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.00</td>\n",
       "      <td>100</td>\n",
       "      <td>0.00</td>\n",
       "      <td>100</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男1000米跑成绩  男50米跑  男50米跑成绩    男跳远  男跳远成绩  男体前屈  男体前屈成绩  \\\n",
       "0     1  男     4.13         72   8.88       66  195.0     60    12      74   \n",
       "1     1  男     4.16         70   7.70       78  225.0     74    11      74   \n",
       "2     1  男     4.09         74   8.45       70  218.0     70    14      78   \n",
       "3     1  男     4.21         68   8.05       74  206.0     64    13      76   \n",
       "4     1  男     3.44         85   7.52       78  210.0     66    13      76   \n",
       "..   .. ..      ...        ...    ...      ...    ...    ...   ...     ...   \n",
       "472  17  男     4.23         68   8.27       72  208.0     66    10      72   \n",
       "473  17  男     5.19         40   9.55       50  210.0     66    15      80   \n",
       "474  17  男     3.25        100   7.50       80  252.0     90    13      76   \n",
       "475  17  男     4.39         62   7.81       76  208.0     66    14      78   \n",
       "476  17  男     0.00        100   0.00      100    0.0      0     0      50   \n",
       "\n",
       "     男引体  男引体成绩  男肺活量  男生肺活量成绩     身高    体重  BMI  \n",
       "0      1      0  2785       62  170.0  72.6    0  \n",
       "1      7     60  3133       68  174.0  52.7    0  \n",
       "2      1      0  3901       80  169.0  46.5    0  \n",
       "3      1      0  4946      100  183.0  79.7    0  \n",
       "4      9     68  3538       74  171.0  54.7    0  \n",
       "..   ...    ...   ...      ...    ...   ...  ...  \n",
       "472    0      0  4647      100  176.0  69.5    0  \n",
       "473    6     50  7042      100  177.0  76.0    0  \n",
       "474   13     85  5755      100  181.0  65.0    0  \n",
       "475   11     76  5688      100  172.0  51.7    0  \n",
       "476    0      0     0        0    0.0   0.0    0  \n",
       "\n",
       "[477 rows x 17 columns]"
      ]
     },
     "execution_count": 282,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_man"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "entertaining-saint",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "id": "cleared-winter",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 男子1000米\n",
    "n = rule_1000\n",
    "def boy_1000(x):\n",
    "    for data in rule_1000['成绩']:\n",
    "        if x<=data:\n",
    "            return rule_1000['分数'].loc[rule_1000['成绩']==data].values[0]\n",
    "    return 0 \n",
    "ff = data_man['男1000米跑'].map(boy_1000).values\n",
    "data_man.insert(loc = 3,column='男1000米跑成绩',value=ff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "id": "seventh-sheffield",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 男50米跑\n",
    "df_50 = rule['男50米跑']\n",
    "def boy_50(x):\n",
    "    for data in df_50['成绩']:\n",
    "        if x<=data:\n",
    "            return df_50['分数'].loc[df_50['成绩']==data].values[0]\n",
    "    return 0 \n",
    "boy_fhl(7.4)\n",
    "ff = data_man['男50米跑'].map(boy_50).values\n",
    "data_man.insert(loc = 4,column='男50米跑成绩',value=ff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "id": "technical-regulation",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男50米跑成绩</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>男生肺活量成绩</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>66</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2785</td>\n",
       "      <td>62</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>78</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>3133</td>\n",
       "      <td>68</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>70</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>3901</td>\n",
       "      <td>80</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>74</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>4946</td>\n",
       "      <td>100</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>78</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13</td>\n",
       "      <td>9</td>\n",
       "      <td>3538</td>\n",
       "      <td>74</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.23</td>\n",
       "      <td>8.27</td>\n",
       "      <td>72</td>\n",
       "      <td>208.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>4647</td>\n",
       "      <td>100</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.19</td>\n",
       "      <td>9.55</td>\n",
       "      <td>50</td>\n",
       "      <td>210.0</td>\n",
       "      <td>15</td>\n",
       "      <td>6</td>\n",
       "      <td>7042</td>\n",
       "      <td>100</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.25</td>\n",
       "      <td>7.50</td>\n",
       "      <td>80</td>\n",
       "      <td>252.0</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>5755</td>\n",
       "      <td>100</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.39</td>\n",
       "      <td>7.81</td>\n",
       "      <td>76</td>\n",
       "      <td>208.0</td>\n",
       "      <td>14</td>\n",
       "      <td>11</td>\n",
       "      <td>5688</td>\n",
       "      <td>100</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>100</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男50米跑  男50米跑成绩    男跳远  男体前屈  男引体  男肺活量  男生肺活量成绩     身高  \\\n",
       "0     1  男     4.13   8.88       66  195.0    12    1  2785       62  170.0   \n",
       "1     1  男     4.16   7.70       78  225.0    11    7  3133       68  174.0   \n",
       "2     1  男     4.09   8.45       70  218.0    14    1  3901       80  169.0   \n",
       "3     1  男     4.21   8.05       74  206.0    13    1  4946      100  183.0   \n",
       "4     1  男     3.44   7.52       78  210.0    13    9  3538       74  171.0   \n",
       "..   .. ..      ...    ...      ...    ...   ...  ...   ...      ...    ...   \n",
       "472  17  男     4.23   8.27       72  208.0    10    0  4647      100  176.0   \n",
       "473  17  男     5.19   9.55       50  210.0    15    6  7042      100  177.0   \n",
       "474  17  男     3.25   7.50       80  252.0    13   13  5755      100  181.0   \n",
       "475  17  男     4.39   7.81       76  208.0    14   11  5688      100  172.0   \n",
       "476  17  男     0.00   0.00      100    0.0     0    0     0        0    0.0   \n",
       "\n",
       "       体重  BMI  \n",
       "0    72.6    0  \n",
       "1    52.7    0  \n",
       "2    46.5    0  \n",
       "3    79.7    0  \n",
       "4    54.7    0  \n",
       "..    ...  ...  \n",
       "472  69.5    0  \n",
       "473  76.0    0  \n",
       "474  65.0    0  \n",
       "475  51.7    0  \n",
       "476   0.0    0  \n",
       "\n",
       "[477 rows x 13 columns]"
      ]
     },
     "execution_count": 222,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_man"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "entertaining-disney",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "'<=' not supported between instances of 'float' and 'str'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-176-903ccfa3b3e8>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      6\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mdf_1000\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'分数'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mdf_1000\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'成绩'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m==\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[1;36m0\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 8\u001b[1;33m \u001b[0mboy_1000\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m7.4\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      9\u001b[0m \u001b[1;31m# ff = data_man['男1000米跑'].map(boy_fhl).values\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     10\u001b[0m \u001b[1;31m# data_man.insert(loc = 3,column='男生肺活量成绩',value=ff)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-176-903ccfa3b3e8>\u001b[0m in \u001b[0;36mboy_1000\u001b[1;34m(x)\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mboy_1000\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mx\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m     \u001b[1;32mfor\u001b[0m \u001b[0mdata\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mdf_1000\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'成绩'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m         \u001b[1;32mif\u001b[0m \u001b[0mx\u001b[0m\u001b[1;33m<=\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mdf_1000\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'分数'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mdf_1000\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'成绩'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m==\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[1;36m0\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: '<=' not supported between instances of 'float' and 'str'"
     ]
    }
   ],
   "source": [
    "# # 男1000米跑\n",
    "# df_1000 = rule['男1000米跑']\n",
    "# def boy_1000(x):\n",
    "#     for data in df_1000['成绩']:\n",
    "#         if x<=data:\n",
    "#             return df_1000['分数'].loc[df_1000['成绩']==data].values[0]\n",
    "#     return 0 \n",
    "# boy_1000(7.4)\n",
    "# ff = data_man['男1000米跑'].map(boy_fhl).values\n",
    "# data_man.insert(loc = 3,column='男生肺活量成绩',value=ff)\n",
    "# ff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "id": "micro-scene",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 男跳远 越远越好\n",
    "n = rule['男跳远']\n",
    "def boy_ty(x):\n",
    "    for data in n['成绩']:\n",
    "        if x>=data:\n",
    "            return n['分数'].loc[n['成绩']==data].values[0]\n",
    "    return 0 \n",
    "ff = data_man['男跳远'].map(boy_ty).values\n",
    "data_man.insert(loc = 6,column='男跳远成绩',value=ff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "id": "otherwise-today",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 男体前屈\n",
    "n = rule['男体前屈']\n",
    "def boy_tqq(x):\n",
    "    for data in n['成绩']:\n",
    "        if x>=data:\n",
    "            return n['分数'].loc[n['成绩']==data].values[0]\n",
    "    return 0 \n",
    "ff = data_man['男体前屈'].map(boy_tqq).values\n",
    "data_man.insert(loc = 8,column='男体前屈成绩',value=ff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "id": "indoor-serial",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 男引体\n",
    "n = rule['男引体']\n",
    "def boy_yt(x):\n",
    "    for data in n['成绩']:\n",
    "        if x>=data:\n",
    "            return n['分数'].loc[n['成绩']==data].values[0]\n",
    "    return 0 \n",
    "ff = data_man['男引体'].map(boy_yt).values\n",
    "data_man.insert(loc = 10,column='男引体成绩',value=ff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "id": "outside-junior",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男50米跑成绩</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男跳远成绩</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男体前屈成绩</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男引体成绩</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>男生肺活量成绩</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>66</td>\n",
       "      <td>195.0</td>\n",
       "      <td>60</td>\n",
       "      <td>12</td>\n",
       "      <td>74</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2785</td>\n",
       "      <td>62</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>78</td>\n",
       "      <td>225.0</td>\n",
       "      <td>74</td>\n",
       "      <td>11</td>\n",
       "      <td>74</td>\n",
       "      <td>7</td>\n",
       "      <td>60</td>\n",
       "      <td>3133</td>\n",
       "      <td>68</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>70</td>\n",
       "      <td>218.0</td>\n",
       "      <td>70</td>\n",
       "      <td>14</td>\n",
       "      <td>78</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3901</td>\n",
       "      <td>80</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>74</td>\n",
       "      <td>206.0</td>\n",
       "      <td>64</td>\n",
       "      <td>13</td>\n",
       "      <td>76</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4946</td>\n",
       "      <td>100</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>78</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66</td>\n",
       "      <td>13</td>\n",
       "      <td>76</td>\n",
       "      <td>9</td>\n",
       "      <td>68</td>\n",
       "      <td>3538</td>\n",
       "      <td>74</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.23</td>\n",
       "      <td>8.27</td>\n",
       "      <td>72</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66</td>\n",
       "      <td>10</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4647</td>\n",
       "      <td>100</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.19</td>\n",
       "      <td>9.55</td>\n",
       "      <td>50</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66</td>\n",
       "      <td>15</td>\n",
       "      <td>80</td>\n",
       "      <td>6</td>\n",
       "      <td>50</td>\n",
       "      <td>7042</td>\n",
       "      <td>100</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.25</td>\n",
       "      <td>7.50</td>\n",
       "      <td>80</td>\n",
       "      <td>252.0</td>\n",
       "      <td>90</td>\n",
       "      <td>13</td>\n",
       "      <td>76</td>\n",
       "      <td>13</td>\n",
       "      <td>85</td>\n",
       "      <td>5755</td>\n",
       "      <td>100</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.39</td>\n",
       "      <td>7.81</td>\n",
       "      <td>76</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66</td>\n",
       "      <td>14</td>\n",
       "      <td>78</td>\n",
       "      <td>11</td>\n",
       "      <td>76</td>\n",
       "      <td>5688</td>\n",
       "      <td>100</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>100</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男50米跑  男50米跑成绩    男跳远  男跳远成绩  男体前屈  男体前屈成绩  男引体  男引体成绩  \\\n",
       "0     1  男     4.13   8.88       66  195.0     60    12      74    1      0   \n",
       "1     1  男     4.16   7.70       78  225.0     74    11      74    7     60   \n",
       "2     1  男     4.09   8.45       70  218.0     70    14      78    1      0   \n",
       "3     1  男     4.21   8.05       74  206.0     64    13      76    1      0   \n",
       "4     1  男     3.44   7.52       78  210.0     66    13      76    9     68   \n",
       "..   .. ..      ...    ...      ...    ...    ...   ...     ...  ...    ...   \n",
       "472  17  男     4.23   8.27       72  208.0     66    10      72    0      0   \n",
       "473  17  男     5.19   9.55       50  210.0     66    15      80    6     50   \n",
       "474  17  男     3.25   7.50       80  252.0     90    13      76   13     85   \n",
       "475  17  男     4.39   7.81       76  208.0     66    14      78   11     76   \n",
       "476  17  男     0.00   0.00      100    0.0      0     0      50    0      0   \n",
       "\n",
       "     男肺活量  男生肺活量成绩     身高    体重  BMI  \n",
       "0    2785       62  170.0  72.6    0  \n",
       "1    3133       68  174.0  52.7    0  \n",
       "2    3901       80  169.0  46.5    0  \n",
       "3    4946      100  183.0  79.7    0  \n",
       "4    3538       74  171.0  54.7    0  \n",
       "..    ...      ...    ...   ...  ...  \n",
       "472  4647      100  176.0  69.5    0  \n",
       "473  7042      100  177.0  76.0    0  \n",
       "474  5755      100  181.0  65.0    0  \n",
       "475  5688      100  172.0  51.7    0  \n",
       "476     0        0    0.0   0.0    0  \n",
       "\n",
       "[477 rows x 16 columns]"
      ]
     },
     "execution_count": 239,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 289,
   "id": "harmful-street",
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "cannot insert 女800米跑成绩, already exists",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-289-ce1140457ed3>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      7\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[1;36m0\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      8\u001b[0m \u001b[0mff\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdata_woman\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'女800米跑'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmap\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mboy_800\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 9\u001b[1;33m \u001b[0mdata_woman\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mloc\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;36m3\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mcolumn\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'女800米跑成绩'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mff\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     10\u001b[0m \u001b[0mdata_womana\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\python37\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36minsert\u001b[1;34m(self, loc, column, value, allow_duplicates)\u001b[0m\n\u001b[0;32m   3626\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_ensure_valid_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3627\u001b[0m         \u001b[0mvalue\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_sanitize_column\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcolumn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mbroadcast\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3628\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_mgr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcolumn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mallow_duplicates\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mallow_duplicates\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   3629\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3630\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0massign\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m->\u001b[0m \u001b[1;34m\"DataFrame\"\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\python37\\lib\\site-packages\\pandas\\core\\internals\\managers.py\u001b[0m in \u001b[0;36minsert\u001b[1;34m(self, loc, item, value, allow_duplicates)\u001b[0m\n\u001b[0;32m   1184\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mallow_duplicates\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mitem\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1185\u001b[0m             \u001b[1;31m# Should this be a different kind of error??\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1186\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mf\"cannot insert {item}, already exists\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1187\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1188\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mint\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mValueError\u001b[0m: cannot insert 女800米跑成绩, already exists"
     ]
    }
   ],
   "source": [
    "# 女子800米\n",
    "n = rule_800\n",
    "def boy_800(x):\n",
    "    for data in rule_800['成绩']:\n",
    "        if x<=data:\n",
    "            return rule_800['分数'].loc[rule_800['成绩']==data].values[0]\n",
    "    return 0 \n",
    "ff = data_woman['女800米跑'].map(boy_800).values\n",
    "data_woman.insert(loc = 3,column='女800米跑成绩',value=ff)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 293,
   "id": "straight-railway",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 593 entries, 0 to 592\n",
      "Data columns (total 17 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   班级        593 non-null    int64  \n",
      " 1   性别        593 non-null    object \n",
      " 2   女800米跑    593 non-null    float64\n",
      " 3   女800米跑成绩  593 non-null    int64  \n",
      " 4   女50米跑     593 non-null    float64\n",
      " 5   女50米跑成绩   593 non-null    int64  \n",
      " 6   女跳远       593 non-null    float64\n",
      " 7   女跳远成绩     593 non-null    int64  \n",
      " 8   女体前屈      593 non-null    int64  \n",
      " 9   女前屈成绩     593 non-null    int64  \n",
      " 10  女仰卧       593 non-null    int64  \n",
      " 11  女仰卧成绩     593 non-null    int64  \n",
      " 12  女肺活量      593 non-null    int64  \n",
      " 13  女生肺活量成绩   593 non-null    int64  \n",
      " 14  身高        593 non-null    float64\n",
      " 15  体重        593 non-null    float64\n",
      " 16  BMI       593 non-null    int64  \n",
      "dtypes: float64(5), int64(11), object(1)\n",
      "memory usage: 78.9+ KB\n"
     ]
    }
   ],
   "source": [
    "data_woman.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "id": "documented-ceramic",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 女生\n",
    "df_fhl = rule['女肺活量']\n",
    "def boy_fhl(x):\n",
    "    for data in df_fhl['成绩']:\n",
    "        if x>=data:\n",
    "            return df_fhl['分数'].loc[df_fhl['成绩']==data].values[0]\n",
    "    return 0 \n",
    "ff = data_woman['女肺活量'].map(boy_fhl).values\n",
    "data_woman.insert(loc = 8,column='女生肺活量成绩',value=ff)\n",
    "# [3,5,7,9,11,13] 男100米跑，男50米跑，男跳远，男体前屈，男引体，男肺活量 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "id": "choice-testimony",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 女50米跑\n",
    "df_50 = rule['女50米跑']\n",
    "def boy_50(x):\n",
    "    for data in df_50['成绩']:\n",
    "        if x<=data:\n",
    "            return df_50['分数'].loc[df_50['成绩']==data].values[0]\n",
    "    return 0 \n",
    "ff = data_woman['女50米跑'].map(boy_50).values\n",
    "data_woman.insert(loc = 4,column='女50米跑成绩',value=ff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "id": "heard-peter",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 女仰卧 越远越好\n",
    "n = rule['女仰卧']\n",
    "def boy_ty(x):\n",
    "    for data in n['成绩']:\n",
    "        if x>=data:\n",
    "            return n['分数'].loc[n['成绩']==data].values[0]\n",
    "    return 0 \n",
    "ff = data_woman['女仰卧'].map(boy_ty).values\n",
    "data_woman.insert(loc = 8,column='女仰卧成绩',value=ff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "id": "apart-north",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_woman = data_woman.drop(labels='女仰卧成绩',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "id": "retained-sodium",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 男跳远 越远越好\n",
    "n = rule['女跳远']\n",
    "def boy_ty(x):\n",
    "    for data in n['成绩']:\n",
    "        if x>=data:\n",
    "            return n['分数'].loc[n['成绩']==data].values[0]\n",
    "    return 0 \n",
    "ff = data_woman['女跳远'].map(boy_ty).values\n",
    "data_woman.insert(loc = 6,column='女跳远成绩',value=ff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "id": "catholic-accounting",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女50米跑成绩</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女跳远成绩</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女前屈成绩</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女仰卧成绩</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>女生肺活量成绩</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>72</td>\n",
       "      <td>185.0</td>\n",
       "      <td>85</td>\n",
       "      <td>16</td>\n",
       "      <td>76</td>\n",
       "      <td>48</td>\n",
       "      <td>85</td>\n",
       "      <td>3775</td>\n",
       "      <td>100</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>10</td>\n",
       "      <td>148.0</td>\n",
       "      <td>60</td>\n",
       "      <td>9</td>\n",
       "      <td>66</td>\n",
       "      <td>29</td>\n",
       "      <td>66</td>\n",
       "      <td>3683</td>\n",
       "      <td>100</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60</td>\n",
       "      <td>7</td>\n",
       "      <td>64</td>\n",
       "      <td>40</td>\n",
       "      <td>76</td>\n",
       "      <td>3331</td>\n",
       "      <td>100</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>70</td>\n",
       "      <td>172.0</td>\n",
       "      <td>76</td>\n",
       "      <td>21</td>\n",
       "      <td>90</td>\n",
       "      <td>46</td>\n",
       "      <td>85</td>\n",
       "      <td>3701</td>\n",
       "      <td>100</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>68</td>\n",
       "      <td>145.0</td>\n",
       "      <td>50</td>\n",
       "      <td>8</td>\n",
       "      <td>64</td>\n",
       "      <td>34</td>\n",
       "      <td>70</td>\n",
       "      <td>3592</td>\n",
       "      <td>100</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>9.60</td>\n",
       "      <td>70</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60</td>\n",
       "      <td>24</td>\n",
       "      <td>95</td>\n",
       "      <td>41</td>\n",
       "      <td>78</td>\n",
       "      <td>2255</td>\n",
       "      <td>70</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60</td>\n",
       "      <td>13</td>\n",
       "      <td>72</td>\n",
       "      <td>36</td>\n",
       "      <td>72</td>\n",
       "      <td>2937</td>\n",
       "      <td>85</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64</td>\n",
       "      <td>152.0</td>\n",
       "      <td>62</td>\n",
       "      <td>15</td>\n",
       "      <td>76</td>\n",
       "      <td>35</td>\n",
       "      <td>72</td>\n",
       "      <td>2592</td>\n",
       "      <td>76</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>9.67</td>\n",
       "      <td>68</td>\n",
       "      <td>165.0</td>\n",
       "      <td>70</td>\n",
       "      <td>10</td>\n",
       "      <td>68</td>\n",
       "      <td>41</td>\n",
       "      <td>78</td>\n",
       "      <td>1829</td>\n",
       "      <td>60</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>9.09</td>\n",
       "      <td>74</td>\n",
       "      <td>180.0</td>\n",
       "      <td>80</td>\n",
       "      <td>10</td>\n",
       "      <td>68</td>\n",
       "      <td>46</td>\n",
       "      <td>85</td>\n",
       "      <td>2962</td>\n",
       "      <td>85</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑  女50米跑  女50米跑成绩    女跳远  女跳远成绩  女体前屈  女前屈成绩  女仰卧  女仰卧成绩  \\\n",
       "0     1  女    3.22   9.32       72  185.0     85    16     76   48     85   \n",
       "1     1  女    4.59  11.44       10  148.0     60     9     66   29     66   \n",
       "2     1  女    3.46  13.40        0  150.0     60     7     64   40     76   \n",
       "3     1  女    3.39   9.52       70  172.0     76    21     90   46     85   \n",
       "4     1  女    3.43   9.79       68  145.0     50     8     64   34     70   \n",
       "..   .. ..     ...    ...      ...    ...    ...   ...    ...  ...    ...   \n",
       "588  17  女    3.51   9.60       70  150.0     60    24     95   41     78   \n",
       "589  17  女    4.00  10.18       64  150.0     60    13     72   36     72   \n",
       "590  17  女    3.45  10.18       64  152.0     62    15     76   35     72   \n",
       "591  17  女    4.01   9.67       68  165.0     70    10     68   41     78   \n",
       "592  17  女    4.48   9.09       74  180.0     80    10     68   46     85   \n",
       "\n",
       "     女肺活量  女生肺活量成绩     身高    体重  BMI  \n",
       "0    3775      100  163.0  51.3    0  \n",
       "1    3683      100  163.0  66.6    0  \n",
       "2    3331      100  157.0  60.0    0  \n",
       "3    3701      100  160.0  50.7    0  \n",
       "4    3592      100  167.0  63.9    0  \n",
       "..    ...      ...    ...   ...  ...  \n",
       "588  2255       70  158.0  49.0    0  \n",
       "589  2937       85  161.0  55.7    0  \n",
       "590  2592       76  165.0  48.6    0  \n",
       "591  1829       60  154.0  43.6    0  \n",
       "592  2962       85  162.0  55.3    0  \n",
       "\n",
       "[593 rows x 16 columns]"
      ]
     },
     "execution_count": 260,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "id": "theoretical-insertion",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男50米跑成绩</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男跳远成绩</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男体前屈成绩</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男引体成绩</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>男生肺活量成绩</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>66</td>\n",
       "      <td>195.0</td>\n",
       "      <td>60</td>\n",
       "      <td>12</td>\n",
       "      <td>74</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2785</td>\n",
       "      <td>62</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>78</td>\n",
       "      <td>225.0</td>\n",
       "      <td>74</td>\n",
       "      <td>11</td>\n",
       "      <td>74</td>\n",
       "      <td>7</td>\n",
       "      <td>60</td>\n",
       "      <td>3133</td>\n",
       "      <td>68</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>70</td>\n",
       "      <td>218.0</td>\n",
       "      <td>70</td>\n",
       "      <td>14</td>\n",
       "      <td>78</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3901</td>\n",
       "      <td>80</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>74</td>\n",
       "      <td>206.0</td>\n",
       "      <td>64</td>\n",
       "      <td>13</td>\n",
       "      <td>76</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4946</td>\n",
       "      <td>100</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>78</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66</td>\n",
       "      <td>13</td>\n",
       "      <td>76</td>\n",
       "      <td>9</td>\n",
       "      <td>68</td>\n",
       "      <td>3538</td>\n",
       "      <td>74</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.23</td>\n",
       "      <td>8.27</td>\n",
       "      <td>72</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66</td>\n",
       "      <td>10</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4647</td>\n",
       "      <td>100</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.19</td>\n",
       "      <td>9.55</td>\n",
       "      <td>50</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66</td>\n",
       "      <td>15</td>\n",
       "      <td>80</td>\n",
       "      <td>6</td>\n",
       "      <td>50</td>\n",
       "      <td>7042</td>\n",
       "      <td>100</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.25</td>\n",
       "      <td>7.50</td>\n",
       "      <td>80</td>\n",
       "      <td>252.0</td>\n",
       "      <td>90</td>\n",
       "      <td>13</td>\n",
       "      <td>76</td>\n",
       "      <td>13</td>\n",
       "      <td>85</td>\n",
       "      <td>5755</td>\n",
       "      <td>100</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.39</td>\n",
       "      <td>7.81</td>\n",
       "      <td>76</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66</td>\n",
       "      <td>14</td>\n",
       "      <td>78</td>\n",
       "      <td>11</td>\n",
       "      <td>76</td>\n",
       "      <td>5688</td>\n",
       "      <td>100</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>100</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男50米跑  男50米跑成绩    男跳远  男跳远成绩  男体前屈  男体前屈成绩  男引体  男引体成绩  \\\n",
       "0     1  男     4.13   8.88       66  195.0     60    12      74    1      0   \n",
       "1     1  男     4.16   7.70       78  225.0     74    11      74    7     60   \n",
       "2     1  男     4.09   8.45       70  218.0     70    14      78    1      0   \n",
       "3     1  男     4.21   8.05       74  206.0     64    13      76    1      0   \n",
       "4     1  男     3.44   7.52       78  210.0     66    13      76    9     68   \n",
       "..   .. ..      ...    ...      ...    ...    ...   ...     ...  ...    ...   \n",
       "472  17  男     4.23   8.27       72  208.0     66    10      72    0      0   \n",
       "473  17  男     5.19   9.55       50  210.0     66    15      80    6     50   \n",
       "474  17  男     3.25   7.50       80  252.0     90    13      76   13     85   \n",
       "475  17  男     4.39   7.81       76  208.0     66    14      78   11     76   \n",
       "476  17  男     0.00   0.00      100    0.0      0     0      50    0      0   \n",
       "\n",
       "     男肺活量  男生肺活量成绩     身高    体重  BMI  \n",
       "0    2785       62  170.0  72.6    0  \n",
       "1    3133       68  174.0  52.7    0  \n",
       "2    3901       80  169.0  46.5    0  \n",
       "3    4946      100  183.0  79.7    0  \n",
       "4    3538       74  171.0  54.7    0  \n",
       "..    ...      ...    ...   ...  ...  \n",
       "472  4647      100  176.0  69.5    0  \n",
       "473  7042      100  177.0  76.0    0  \n",
       "474  5755      100  181.0  65.0    0  \n",
       "475  5688      100  172.0  51.7    0  \n",
       "476     0        0    0.0   0.0    0  \n",
       "\n",
       "[477 rows x 16 columns]"
      ]
     },
     "execution_count": 262,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b1 = data_man.copy()\n",
    "b1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 295,
   "id": "international-height",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男1000米跑成绩</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男50米跑成绩</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男跳远成绩</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男体前屈成绩</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男引体成绩</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>男生肺活量成绩</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>72</td>\n",
       "      <td>8.88</td>\n",
       "      <td>66</td>\n",
       "      <td>195.0</td>\n",
       "      <td>60</td>\n",
       "      <td>12</td>\n",
       "      <td>74</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2785</td>\n",
       "      <td>62</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>70</td>\n",
       "      <td>7.70</td>\n",
       "      <td>78</td>\n",
       "      <td>225.0</td>\n",
       "      <td>74</td>\n",
       "      <td>11</td>\n",
       "      <td>74</td>\n",
       "      <td>7</td>\n",
       "      <td>60</td>\n",
       "      <td>3133</td>\n",
       "      <td>68</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>74</td>\n",
       "      <td>8.45</td>\n",
       "      <td>70</td>\n",
       "      <td>218.0</td>\n",
       "      <td>70</td>\n",
       "      <td>14</td>\n",
       "      <td>78</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3901</td>\n",
       "      <td>80</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>68</td>\n",
       "      <td>8.05</td>\n",
       "      <td>74</td>\n",
       "      <td>206.0</td>\n",
       "      <td>64</td>\n",
       "      <td>13</td>\n",
       "      <td>76</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4946</td>\n",
       "      <td>100</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>85</td>\n",
       "      <td>7.52</td>\n",
       "      <td>78</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66</td>\n",
       "      <td>13</td>\n",
       "      <td>76</td>\n",
       "      <td>9</td>\n",
       "      <td>68</td>\n",
       "      <td>3538</td>\n",
       "      <td>74</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.23</td>\n",
       "      <td>68</td>\n",
       "      <td>8.27</td>\n",
       "      <td>72</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66</td>\n",
       "      <td>10</td>\n",
       "      <td>72</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4647</td>\n",
       "      <td>100</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.19</td>\n",
       "      <td>40</td>\n",
       "      <td>9.55</td>\n",
       "      <td>50</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66</td>\n",
       "      <td>15</td>\n",
       "      <td>80</td>\n",
       "      <td>6</td>\n",
       "      <td>50</td>\n",
       "      <td>7042</td>\n",
       "      <td>100</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.25</td>\n",
       "      <td>100</td>\n",
       "      <td>7.50</td>\n",
       "      <td>80</td>\n",
       "      <td>252.0</td>\n",
       "      <td>90</td>\n",
       "      <td>13</td>\n",
       "      <td>76</td>\n",
       "      <td>13</td>\n",
       "      <td>85</td>\n",
       "      <td>5755</td>\n",
       "      <td>100</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.39</td>\n",
       "      <td>62</td>\n",
       "      <td>7.81</td>\n",
       "      <td>76</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66</td>\n",
       "      <td>14</td>\n",
       "      <td>78</td>\n",
       "      <td>11</td>\n",
       "      <td>76</td>\n",
       "      <td>5688</td>\n",
       "      <td>100</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.00</td>\n",
       "      <td>100</td>\n",
       "      <td>0.00</td>\n",
       "      <td>100</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男1000米跑成绩  男50米跑  男50米跑成绩    男跳远  男跳远成绩  男体前屈  男体前屈成绩  \\\n",
       "0     1  男     4.13         72   8.88       66  195.0     60    12      74   \n",
       "1     1  男     4.16         70   7.70       78  225.0     74    11      74   \n",
       "2     1  男     4.09         74   8.45       70  218.0     70    14      78   \n",
       "3     1  男     4.21         68   8.05       74  206.0     64    13      76   \n",
       "4     1  男     3.44         85   7.52       78  210.0     66    13      76   \n",
       "..   .. ..      ...        ...    ...      ...    ...    ...   ...     ...   \n",
       "472  17  男     4.23         68   8.27       72  208.0     66    10      72   \n",
       "473  17  男     5.19         40   9.55       50  210.0     66    15      80   \n",
       "474  17  男     3.25        100   7.50       80  252.0     90    13      76   \n",
       "475  17  男     4.39         62   7.81       76  208.0     66    14      78   \n",
       "476  17  男     0.00        100   0.00      100    0.0      0     0      50   \n",
       "\n",
       "     男引体  男引体成绩  男肺活量  男生肺活量成绩     身高    体重  BMI  \n",
       "0      1      0  2785       62  170.0  72.6    0  \n",
       "1      7     60  3133       68  174.0  52.7    0  \n",
       "2      1      0  3901       80  169.0  46.5    0  \n",
       "3      1      0  4946      100  183.0  79.7    0  \n",
       "4      9     68  3538       74  171.0  54.7    0  \n",
       "..   ...    ...   ...      ...    ...   ...  ...  \n",
       "472    0      0  4647      100  176.0  69.5    0  \n",
       "473    6     50  7042      100  177.0  76.0    0  \n",
       "474   13     85  5755      100  181.0  65.0    0  \n",
       "475   11     76  5688      100  172.0  51.7    0  \n",
       "476    0      0     0        0    0.0   0.0    0  \n",
       "\n",
       "[477 rows x 17 columns]"
      ]
     },
     "execution_count": 295,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_man"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "id": "fatty-sucking",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女800米跑成绩</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女50米跑成绩</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女跳远成绩</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女前屈成绩</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女仰卧成绩</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>女生肺活量成绩</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>100</td>\n",
       "      <td>9.32</td>\n",
       "      <td>72</td>\n",
       "      <td>185.0</td>\n",
       "      <td>85</td>\n",
       "      <td>16</td>\n",
       "      <td>76</td>\n",
       "      <td>48</td>\n",
       "      <td>85</td>\n",
       "      <td>3775</td>\n",
       "      <td>100</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>40</td>\n",
       "      <td>11.44</td>\n",
       "      <td>10</td>\n",
       "      <td>148.0</td>\n",
       "      <td>60</td>\n",
       "      <td>9</td>\n",
       "      <td>66</td>\n",
       "      <td>29</td>\n",
       "      <td>66</td>\n",
       "      <td>3683</td>\n",
       "      <td>100</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>80</td>\n",
       "      <td>13.40</td>\n",
       "      <td>0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60</td>\n",
       "      <td>7</td>\n",
       "      <td>64</td>\n",
       "      <td>40</td>\n",
       "      <td>76</td>\n",
       "      <td>3331</td>\n",
       "      <td>100</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>85</td>\n",
       "      <td>9.52</td>\n",
       "      <td>70</td>\n",
       "      <td>172.0</td>\n",
       "      <td>76</td>\n",
       "      <td>21</td>\n",
       "      <td>90</td>\n",
       "      <td>46</td>\n",
       "      <td>85</td>\n",
       "      <td>3701</td>\n",
       "      <td>100</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>85</td>\n",
       "      <td>9.79</td>\n",
       "      <td>68</td>\n",
       "      <td>145.0</td>\n",
       "      <td>50</td>\n",
       "      <td>8</td>\n",
       "      <td>64</td>\n",
       "      <td>34</td>\n",
       "      <td>70</td>\n",
       "      <td>3592</td>\n",
       "      <td>100</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>78</td>\n",
       "      <td>9.60</td>\n",
       "      <td>70</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60</td>\n",
       "      <td>24</td>\n",
       "      <td>95</td>\n",
       "      <td>41</td>\n",
       "      <td>78</td>\n",
       "      <td>2255</td>\n",
       "      <td>70</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>76</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60</td>\n",
       "      <td>13</td>\n",
       "      <td>72</td>\n",
       "      <td>36</td>\n",
       "      <td>72</td>\n",
       "      <td>2937</td>\n",
       "      <td>85</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>80</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64</td>\n",
       "      <td>152.0</td>\n",
       "      <td>62</td>\n",
       "      <td>15</td>\n",
       "      <td>76</td>\n",
       "      <td>35</td>\n",
       "      <td>72</td>\n",
       "      <td>2592</td>\n",
       "      <td>76</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>74</td>\n",
       "      <td>9.67</td>\n",
       "      <td>68</td>\n",
       "      <td>165.0</td>\n",
       "      <td>70</td>\n",
       "      <td>10</td>\n",
       "      <td>68</td>\n",
       "      <td>41</td>\n",
       "      <td>78</td>\n",
       "      <td>1829</td>\n",
       "      <td>60</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>50</td>\n",
       "      <td>9.09</td>\n",
       "      <td>74</td>\n",
       "      <td>180.0</td>\n",
       "      <td>80</td>\n",
       "      <td>10</td>\n",
       "      <td>68</td>\n",
       "      <td>46</td>\n",
       "      <td>85</td>\n",
       "      <td>2962</td>\n",
       "      <td>85</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑  女800米跑成绩  女50米跑  女50米跑成绩    女跳远  女跳远成绩  女体前屈  女前屈成绩  女仰卧  \\\n",
       "0     1  女    3.22       100   9.32       72  185.0     85    16     76   48   \n",
       "1     1  女    4.59        40  11.44       10  148.0     60     9     66   29   \n",
       "2     1  女    3.46        80  13.40        0  150.0     60     7     64   40   \n",
       "3     1  女    3.39        85   9.52       70  172.0     76    21     90   46   \n",
       "4     1  女    3.43        85   9.79       68  145.0     50     8     64   34   \n",
       "..   .. ..     ...       ...    ...      ...    ...    ...   ...    ...  ...   \n",
       "588  17  女    3.51        78   9.60       70  150.0     60    24     95   41   \n",
       "589  17  女    4.00        76  10.18       64  150.0     60    13     72   36   \n",
       "590  17  女    3.45        80  10.18       64  152.0     62    15     76   35   \n",
       "591  17  女    4.01        74   9.67       68  165.0     70    10     68   41   \n",
       "592  17  女    4.48        50   9.09       74  180.0     80    10     68   46   \n",
       "\n",
       "     女仰卧成绩  女肺活量  女生肺活量成绩     身高    体重  BMI  \n",
       "0       85  3775      100  163.0  51.3    0  \n",
       "1       66  3683      100  163.0  66.6    0  \n",
       "2       76  3331      100  157.0  60.0    0  \n",
       "3       85  3701      100  160.0  50.7    0  \n",
       "4       70  3592      100  167.0  63.9    0  \n",
       "..     ...   ...      ...    ...   ...  ...  \n",
       "588     78  2255       70  158.0  49.0    0  \n",
       "589     72  2937       85  161.0  55.7    0  \n",
       "590     72  2592       76  165.0  48.6    0  \n",
       "591     78  1829       60  154.0  43.6    0  \n",
       "592     85  2962       85  162.0  55.3    0  \n",
       "\n",
       "[593 rows x 17 columns]"
      ]
     },
     "execution_count": 296,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_woman"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 299,
   "id": "yellow-sixth",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导出为excel\n",
    "with pd.ExcelWriter('./体育成绩及得分.xls') as writer:\n",
    "    data_man.to_excel(writer,sheet_name='男',header=True,index=False)\n",
    "    data_woman.to_excel(writer,sheet_name='女',header=True,index=False)\n",
    "# data_man.to_excel('./体育成绩及得分.xlsx',\n",
    "#                   sheet_name='男',\n",
    "#                   header=True,\n",
    "#                   index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "secondary-settlement",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
